set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_whole_decide_duty_dynamic_dt
select
     base.waybill_no                             --运单号
    ,base.platform_effect                        --自有平台时效标准(兜底路由)
    ,step.ziyou_platform_effect                  --自由平台时效标准(推荐路由)
    ,step.if_platform_standard                   --自有平台时效是否达成:1达成,0未达成(推荐路由)
    ,base.if_doudi_platform_standard             --自有平台时效是否达成:1达成,0未达成(兜底路由)
    ,step.cainiao_platform_name                  --七星潭平台名称
    ,step.cainiao_platform_effect                --七星潭平台时效标准
    ,step.if_cainiao_platform_pres_standard      --七星潭平台时效是否达成:1达成,0未达成
    ,case when if_cainiao_platform_pres_standard = 1 then null
          when scan_site_no = 1 and ziyou_platform_effect is null then '静态路由缺失'
          when scan_site_no = 1 and (actual_sign_time is not null
          and actual_sign_code <> lasted_network_code and lasted_network_code is not null
          and if_reback <> 1 and if_transfer <> 1) or if_taitc = 1 then '地址解析错误'
          when scan_site_no = 1 and fast_total_days > cainiao_platform_effect then '静态慢于' --全部慢于才算慢于
          when scan_site_no = 1 and fast_total_days <= cainiao_platform_effect and total_days > cainiao_platform_effect then '静态慢于(多班次)' --20230920新增责任
          when scan_site_no = 1 and total_days + extra_time > cainiao_platform_effect  then '乡镇加时'
          end as first_duty_type --七星潭静态环节责任
    ,case when if_platform_standard= 1 then null
          when scan_site_no = 1 and (actual_sign_time is not null
          and actual_sign_code <> lasted_network_code and lasted_network_code is not null
          and if_reback <> 1 and if_transfer <> 1) or if_taitc = 1 then '地址解析错误'
          end as first_ziyou_duty_type --自由平台静态环节责任责任
    ,step.if_intercept                           --是否拦截件:1是,0否
    ,step.if_reback                              --是否退回件:1是,0否
    ,step.if_transfer                            --是否转寄件:1是,0否
    ,step.start_city_code                        --始发城市编码
    ,step.start_city_name                        --始发城市名称
    ,step.sign_city_code                         --签收城市编码
    ,step.sign_city_name                         --签收城市名称
    ,step.taking_time                            --揽收时间
    ,step.deliver_time                           --出仓时间
    ,step.aging_sign_time                        --时效签收时间
    ,step.actual_sign_time                       --实际签收时间
    ,step.economic_circle_name                   --经济圈名称
    ,step.scan_site_no                           --当前站点编号
    ,step.scan_site_code                         --当前站点编码
    ,step.scan_site_name                         --当前站点名称
    ,step.scan_site_type                         --当前站点类型
    ,step.scan_site_arrival_time                 --当前站点到件时间
    ,step.scan_site_send_time                    --当前站点发件时间
    ,step.scan_site_actual_arrival_time          --当前站点发件任务号实际到车时间
    ,step.scan_site_actual_send_time             --当前站点发件任务号实际发车时间
    ,step.scan_site_planned_departure_time       --当前站点发件任务号规划发车时间
    ,step.scan_site_planned_arrival_time         --当前站点发件任务号规划到车时间
    ,step.scan_site_seal_time                    --当前站点发件任务号封车时间
    ,step.scan_site_actual_shipment_no           --当前站点发件任务号实际发车任务号
    ,step.scan_site_actual_shipment_name         --当前站点发件任务号实际发车任务名
    ,step.scan_site_if_stop                      --当前站是否经停
    ,step.scan_site_if_bagg                      --当前站点是否建包扫描
    ,step.scan_site_arrival_bagg_code            --当前站点到件包号
    ,step.scan_site_arrival_bagg_name            --当前站点到件包名
    ,step.scan_site_send_bagg_code               --当前站点发件包号
    ,step.scan_site_send_bagg_name               --当前站点发件包名
    ,step.scan_site_send_bagg_addr_code          --当前站发件建包目的地编码
    ,step.scan_site_send_bagg_addr_name          --当前站发件建包目的地名称
    ,step.scan_site_arrival_bagg_addr_code       --当前站到件建包目的地编码
    ,step.scan_site_arrival_bagg_addr_name       --当前站到件建包目的地名称
    ,step.actual_diff_time                       --当前站点与下一站实际时间差(小时)
    ,step.actual_node                            --当前站点简称(实际节点)
    ,step.next_site_code                         --下一站编码
    ,step.next_site_name                         --下一站名称
    ,step.next_site_type                         --下一站类型
    ,step.next_site_arrival_time                 --下一站到件时间
    ,step.next_site_send_time                    --下一站发件时间
    ,step.next_site_actual_arrival_time          --下一站发件任务号到车时间
    ,step.next_site_actual_send_time             --下一站发件任务号发车时间
    ,step.next_site_seal_time                    --下一站发件任务号封车时间
    ,step.next_site_planned_departure_time       --下一站发件任务号规划发车时间
    ,step.next_site_planned_arrival_time         --下一站发件任务号规划到车时间
    ,step.next_site_if_stop                      --下一站发件是否经停
    ,step.pre_site_code                          --上一站编码
    ,step.pre_site_name                          --上一站名称
    ,step.pre_site_type                          --上一站类型
    ,step.pre_site_arrival_time                  --上一站到件时间
    ,step.pre_site_send_time                     --上一站发件时间
    ,step.pre_site_actual_arrival_time           --上一站发件任务号到车时间
    ,step.pre_site_actual_send_time              --上一站发件任务号发车时间
    ,step.pre_site_seal_time                     --上一站发件任务号封车时间
    ,step.pre_site_planned_departure_time        --上一站发件任务号规划发车时间
    ,step.pre_site_planned_arrival_time          --上一站发件任务号规划到车时间
    ,step.pre_scan_if_stop                       --上一站是否经停
    ,step.first_center_actual_arrival_time       --始发中心实际到车时间
    ,step.first_center_actual_send_time          --始发中心实际发车时间
    ,step.first_network_code                     --始发网点编码
    ,step.first_network_name                     --始发网点名称
    ,step.sign_network_code                      --签收网点编码
    ,step.sign_network_name                      --签收网点名称
    ,step.last_network_code                      --目的网点编码
    ,step.last_network_name                      --目的网点名称
    ,step.first_code                             --一段码
    ,step.lasted_center_code                     --一段码目的中心编码
    ,step.second_code                            --二段码
    ,step.lasted_network_code                    --二段码目的网点
    ,step.type                                   --场景划分
    ,base.total_days                             --T+时效
    ,base.route_id                               --路由唯一键
    ,base.if_taking_off                          --是否截单后揽收
    ,step.actual_effect                          --动态时效 20230216新增
    ,step.pre_site_stop_actual_send_time         --经停站点上一站实际发车时间_20230227新增
    ,step.pre_site_stop_planned_departure_time   --经停站点上一站规划发车时间_20230227新增
    ,step.next_site_stop_code                    --经停站点下一站点编码_20230227新增
    ,step.next_site_stop_name                    --经停站点下一站点名称_20230227新增
    ,step.next_site_stop_type                    --经停站点下一站点类型_20230227新增
    ,step.next_site_stop_actual_arrival_time     --经停站点下一站点实际到车时间_20230227新增
    ,step.next_site_stop_planned_arrival_time    --经停站点下一站点规划到车时间_20230227新增
    ,step.next_stop_site_actual_send_time        --下一经停站点规划发车时间_20230227新增
    ,step.next_stop_site_planned_departure_time  --下一经停站点规划到车时间_20230227新增
    ,step.next_stop_site_actual_arrival_time     --下一经停站点实际到车时间_20230227新增
    ,step.next_stop_site_planned_arrival_time    --下一经停站点实际发车时间_20230227新增
    ,step.next_l2_site_code                      --下下一站编码
    ,step.if_taitc                               --问题件抬头错标识
    ,step.order_source_code                      --订单来源编码
    ,step.order_source_name                      --订单来源名称
    ,step.if_reback_reach                        --退转拦截是否达成
    ,step.reback_apply_time                      --退转件申请时间
    ,step.intercept_registration_time            --拦截件登记时间
    ,step.cainiao_plan_sign_time                 --达成率菜鸟规划签收时间
    ,step.ziyou_plan_sign_time                   --达成率自有规划签收时间
    ,base.taking_date                            --揽收日期(含截单后揽收)
    ,step.start_center_code                      --始发中心编码
    ,step.start_center_name                      --始发中心名称
    ,step.end_center_code                        --目的中心编码
    ,step.end_center_name                        --目的中心名称
    ,step.customer_code                          --客户编码
    ,step.customer_name                          --客户名称
    ,step.scan_site_bagg_user_code               --当前站点建包扫描员编号
    ,step.scan_site_bagg_user_name               --当前站点建包扫描员名称
    ,step.pre_site_stop_code                     --经停站点上一站编码
    ,step.pre_site_stop_name                     --经停站点上一站名称
    ,step.pre_site_stop_type                     --经停站点上一站类型
    ,step.scan_site_ship_table_type              --当前站点发件任务号任务类型1干线,2支线
    ,step.scan_site_ship_next_network_code       --发件任务号任务下一站点编码
    ,step.scan_site_arrival_shipment_no          --当前站点到件任务号 
    ,step.pre_site_send_shipment_no              --上一站点发件任务号 
    ,step.scan_site_ship_nodes                   --当前站点发件任务号节点数20231214新增
    ,case when by_route.route_code is not null then 1 else 0 end as scan_site_if_back_route
    ,step.if_trans_mail
    ,step.scan_site_min_time
    ,step.pre_l2_site_code
    ,step.dt
from (
    select *
    from (
        select
             waybill_no                             --运单号
            ,platform_effect                        --自有平台时效标准(兜底路由)
            ,case when aging_sign_time is null and datediff(current_date(),taking_date) >  platform_effect then 0
                  when aging_sign_time is null and datediff(current_date(),taking_date) <= platform_effect then null
                  when aging_sign_time is not null and datediff(to_date(aging_sign_time),taking_date) <= platform_effect then 1
                  when aging_sign_time is not null and datediff(to_date(aging_sign_time),taking_date) >  platform_effect then 0
                  end as if_doudi_platform_standard --自有平台时效是否达成:1达成,0未达成(兜底路由)
            ,taking_time                            --揽收时间
            ,aging_sign_time                        --时效签收时间
            ,first_network_code                     --始发网点编码
            ,first_network_name                     --始发网点名称
            ,last_network_code                      --目的网点编码
            ,last_network_name                      --目的网点名称
            ,total_days                             --T+时效
            ,route_id                               --路由唯一键
            ,if_taking_off                          --是否截单后揽收
            ,taking_date                            --揽收日期(含截单后揽收)
            ,fast_total_days
            ,dt
            ,row_number() over(partition by waybill_no
                                   order by if(unix_timestamp(taking_time) - unix_timestamp(plan_last_taking_time) <= 0, 1, 2)
                                           ,if(if_taking_off = 1,unix_timestamp(taking_time) - unix_timestamp(plan_last_taking_time), 0) desc --如果是截单后揽收,最当天最早的
                                           ,abs(unix_timestamp(taking_time) - unix_timestamp(plan_last_taking_time))
                                           ,total_days asc
                                           ,split(match_nodes,'-')[1] desc
                                           ,split(match_nodes,'-')[0] desc
                                           ,plan_send_time --规划发车时间
                                           ,route_id desc) as rn
        from (
            select
                 wide.*
                ,route.nodes as static_nodes
                ,size(split(wide.nodes,'-'))  as dynamic_nodes_length --动态长度
                ,size(split(route.nodes,'-')) as static_nodes_length --静态长度
                ,get_duty_route(wide.nodes,nvl(route.nodes,'')) as match_nodes --选取路由
                ,route.route_id                   as route_id   --路由唯一键
                ,route.total_days                 as total_days --T+时效
                ,route.total_days + if(wide.taking_date>to_date(wide.taking_time),1,0) as platform_effect --时效标准
                ,concat(wide.taking_date,' ',route.last_taking_time) as plan_last_taking_time       --当前站点规划揽收结束时间
                ,concat(wide.taking_date,' ',node.lasted_taking_time) as plan_lasted_taking_time   --当前站点规划最晚揽收结束时间
                ,case when max_search_type = min_search_type then max_search_type else min_search_type end as fast_total_days --最快的T+时效
                ,case when taking_time > concat(wide.taking_date,' ',node.lasted_taking_time,':00') then 1 else 0 end as if_taking_off --是否截单后揽收
                ,route.plan_send_time
            from (
                select
                     waybill_no
                    ,max(first_network_code) as first_network_code
                    ,max(first_network_name) as first_network_name
                    ,max(last_network_code ) as last_network_code
                    ,max(last_network_name ) as last_network_name
                    ,max(taking_time ) as taking_time
                    ,max(to_date(taking_time)) as taking_date
                    ,max(aging_sign_time) as aging_sign_time
                    ,regexp_replace(concat_ws('-',sort_array(collect_set(concat_ws(':',scan_site_no,scan_site_code)))),'\\d\:','') as nodes
                    ,dt
                from jms_dm.dm_whole_decide_duty_detail_dt
                where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
                and nvl(scan_site_if_stop ,0) <> 1
                group by waybill_no
                        ,dt
            ) wide
            left join (
                select
                     in_from_code
                    ,out_to_code
                    ,max(search_type) as max_search_type --网点-网点-揽收时间段 最大的时效
                    ,min(search_type) as min_search_type --网点-网点-揽收时间段 最小的时效
                    ,max(last_taking_time) as lasted_taking_time --最晚的揽收结束时间
                    ,dt
                from (
                    select *
                    from (
                        select
                             in_from_code
                            ,out_to_code
                            ,in_from_name
                            ,out_to_name
                            ,last_taking_time
                            ,search_type
                            ,row_number() over(partition by in_from_code,out_to_code,last_taking_time,dt order by search_type) as rn
                            ,dt
                        from jms_dm.dm_route_whole_node_base_dt
                        where dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
                    ) a where a.rn = 1
                ) a group by in_from_code
                            ,out_to_code
                            ,dt
            ) node on node.in_from_code = wide.first_network_code  --始发网点
                  and node.out_to_code = wide.last_network_code    --目的网点
                  and date_add(node.dt,1) = wide.taking_date --揽收日期(含截单后揽收)
            left join (
                select
                     route_id
                    ,max(first_network_code) as first_network_code --始发网点编码
                    ,max(first_network_name) as first_network_name --始发网点名称
                    ,max(last_network_code ) as last_network_code  --目的网点编码
                    ,max(last_network_name ) as last_network_name  --目的网点名称
                    ,max(total_days) as total_days
                    ,max(last_taking_time) as last_taking_time
                    ,regexp_replace(concat_ws('-',sort_array(collect_set(concat_ws(':',scan_site_no,scan_site_code)))),'\\d\:','') as nodes
                    ,regexp_replace(concat_ws('-',sort_array(collect_set(concat_ws('@',scan_site_no,concat(date_add('{{ execution_date | cst_ds }}',send_span_days),' ',plan_send_time,':00'))))),'\\d\@','') as plan_send_time
                    ,dt
                from jms_dm.dm_route_whole_step_dt
                where dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
                 and last_taking_time is not null
                group by route_id
                        ,dt
            ) route on wide.first_network_code = route.first_network_code
                   and wide.last_network_code = route.last_network_code
                   and date_add(route.dt,1) = wide.taking_date --揽收日期(含截单后揽收)
        ) a
    ) a where a.rn = 1
) base
left join (
    select
         *
        ,to_date(taking_time) as taking_date
    from jms_dm.dm_whole_decide_duty_detail_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
) step on base.waybill_no = step.waybill_no
left join (
    select
         start_code
        ,next_code
        ,end_code
        ,route_code
    from jms_dim.dim_yl_tms_route_base_dt
    where dt = '{{ execution_date | cst_ds }}'
    and exception_status not in(2,3)
    and status = 1
    and date(start_date)<='{{ execution_date | cst_ds }}' and date(end_date)>='{{ execution_date | cst_ds }}'
    and route_type = 2
    and route_name is not null
) by_route on step.scan_site_code = by_route.start_code
          and step.next_site_code = by_route.next_code
          and step.end_center_code = by_route.end_code
distribute by dt,pmod(hash(waybill_no),120)
;